package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

import vcp.entity.Car;
import vcp.entity.Customer;
import vcp.entity.Subscription;
import vcp.enums.SiteEnum;
import vcp.enums.SubscriptionType;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.SubscribtionsTable;
import vcp.server.utility.Constants.SubscriptionHistoryTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.SQLQueries;

/**
 * <p>
 * subscription DAO class implements slq queries related to subscription
 * </p>
 * 
 * @author VCP team
 * 
 */
public class SubscriptionDAO implements IGenericDAO<Subscription, Customer>
{
	// ****************************************************************************************************************************
	// Public methods
	// ****************************************************************************************************************************
	/**
	 * finds all subscriptions between given start and end dates
	 * 
	 * @param startDate
	 * @param endDate
	 * @param siteEnum
	 * @return list of subscriptions
	 */
	public List<Subscription> findAllSubscriptionsByDatesPeriod(DateTime startDate, DateTime endDate, SiteEnum siteEnum)
	{
		List<Subscription> listOfSubscription = new ArrayList<>();

		String query = SQLQueries.SELECT_ALL + SQLQueries.FROM + TableNames.SUBSCRIPTION + " " + SQLQueries.WHERE + SubscriptionHistoryTable.SITE_NAME + "=" + "\"" + siteEnum.toString() + "\"" + " "
				+ SQLQueries.OR + SubscribtionsTable.SITE_NAME + " is NULL" + " " + SQLQueries.AND + " " + SQLQueries.DATE + "(" + SubscribtionsTable.START_DATE + ")" + SQLQueries.GTE + "\""
				+ DateTimeFormat.forPattern("yyyy-MM-dd").print(startDate) + "\"" + " " + SQLQueries.AND + SQLQueries.DATE + "(" + SubscribtionsTable.END_DATE + ")" + SQLQueries.LTE + "\""
				+ DateTimeFormat.forPattern("yyyy-MM-dd").print(endDate) + "\"";
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while (set.next())
			{
				Subscription subscription = this.create();

				subscription.setSubscribtionID(set.getInt(SubscribtionsTable.ID));
				subscription.setCustomerID(set.getInt(SubscribtionsTable.CUSTOMER_ID));
				subscription.setSite(SiteEnum.convert(set.getString(SubscribtionsTable.SITE_NAME)));
				subscription.setSubscriptionType(SubscriptionType.convert(set.getString(SubscribtionsTable.TYPE)));
				subscription.setStartDate(new DateTime(set.getTimestamp(SubscribtionsTable.START_DATE)));
				subscription.setEndDate(new DateTime(set.getTimestamp(SubscribtionsTable.END_DATE)));
				subscription.setEstimatedLeaveDate(new DateTime(set.getTimestamp(SubscribtionsTable.ESTIMATED_LEAVE_DATE)));
				subscription.setArrivalDate(new DateTime(set.getTimestamp(SubscribtionsTable.ARRIVAL_DATE)));
				subscription.setDeleted(false);

				CarDAO carDAO = new CarDAO();
				List<Car> carsInSubscription = carDAO.findAll(subscription);
				subscription.setCars(carsInSubscription);

				listOfSubscription.add(subscription);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return listOfSubscription;
	}

	/**
	 * finds subscriptions that are Ordered ahead
	 * 
	 * @return list of subscriptions
	 */
	public List<Subscription> findAllOrderAheadSubscribers()
	{
		String query = String.format(SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_, TableNames.SUBSCRIPTION, SubscribtionsTable.TYPE + "=" + "\"" + SubscriptionType.ORDER_AHEAD.toString()
				+ "\"");

		List<Subscription> listOfSubscription = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfSubscription = new ArrayList<>();
			while (set.next())
			{
				Subscription subscription = this.create();

				subscription.setSubscribtionID(set.getInt(SubscribtionsTable.ID));
				subscription.setCustomerID(set.getInt(SubscribtionsTable.CUSTOMER_ID));
				subscription.setSite(SiteEnum.convert(set.getString(SubscribtionsTable.SITE_NAME)));
				subscription.setSubscriptionType(SubscriptionType.convert(set.getString(SubscribtionsTable.TYPE)));
				subscription.setStartDate(new DateTime(set.getTimestamp(SubscribtionsTable.START_DATE)));
				subscription.setEndDate(new DateTime(set.getTimestamp(SubscribtionsTable.END_DATE)));
				subscription.setEstimatedLeaveDate(new DateTime(set.getTimestamp(SubscribtionsTable.ESTIMATED_LEAVE_DATE)));
				subscription.setArrivalDate(new DateTime(set.getTimestamp(SubscribtionsTable.ARRIVAL_DATE)));
				subscription.setDeleted(false);

				CarDAO carDAO = new CarDAO();
				List<Car> carsInSubscription = carDAO.findAll(subscription);
				subscription.setCars(carsInSubscription);

				listOfSubscription.add(subscription);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return listOfSubscription;
	}

	/**
	 * finds casual subscribers
	 * 
	 * @return list of subscriptions
	 */
	public List<Subscription> findAllCasualFullSubscribers()
	{
		String query = String.format(SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_, TableNames.SUBSCRIPTION, SubscribtionsTable.TYPE + " like " + "\"%subscriber\"");

		List<Subscription> listOfSubscription = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfSubscription = new ArrayList<>();
			while (set.next())
			{
				Subscription subscription = this.create();

				subscription.setSubscribtionID(set.getInt(SubscribtionsTable.ID));
				subscription.setCustomerID(set.getInt(SubscribtionsTable.CUSTOMER_ID));
				subscription.setSite(SiteEnum.convert(set.getString(SubscribtionsTable.SITE_NAME)));
				subscription.setSubscriptionType(SubscriptionType.convert(set.getString(SubscribtionsTable.TYPE)));
				subscription.setStartDate(new DateTime(set.getTimestamp(SubscribtionsTable.START_DATE)));
				subscription.setEndDate(new DateTime(set.getTimestamp(SubscribtionsTable.END_DATE)));
				subscription.setEstimatedLeaveDate(new DateTime(set.getTimestamp(SubscribtionsTable.ESTIMATED_LEAVE_DATE)));
				subscription.setArrivalDate(new DateTime(set.getTimestamp(SubscribtionsTable.ARRIVAL_DATE)));
				subscription.setDeleted(false);

				CarDAO carDAO = new CarDAO();
				List<Car> carsInSubscription = carDAO.findAll(subscription);
				subscription.setCars(carsInSubscription);

				listOfSubscription.add(subscription);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return listOfSubscription;
	}

	/**
	 * 
	 * @return all subscribers
	 */
	public List<Subscription> findAllSubscriptions()
	{
		List<Subscription> listOfSubscription = new ArrayList<>();

		String query = String.format(SQLQueries.SELECT_ALL_FROM_SINGLETABLE, TableNames.SUBSCRIPTION);

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while (set.next())
			{
				Subscription subscription = this.create();

				subscription.setSubscribtionID(set.getInt(SubscribtionsTable.ID));
				subscription.setCustomerID(set.getInt(SubscribtionsTable.CUSTOMER_ID));
				subscription.setSite(SiteEnum.convert(set.getString(SubscribtionsTable.SITE_NAME)));
				subscription.setSubscriptionType(SubscriptionType.convert(set.getString(SubscribtionsTable.TYPE)));
				subscription.setStartDate(new DateTime(set.getTimestamp(SubscribtionsTable.START_DATE)));
				subscription.setEndDate(new DateTime(set.getTimestamp(SubscribtionsTable.END_DATE)));
				subscription.setEstimatedLeaveDate(new DateTime(set.getTimestamp(SubscribtionsTable.ESTIMATED_LEAVE_DATE)));
				subscription.setArrivalDate(new DateTime(set.getTimestamp(SubscribtionsTable.ARRIVAL_DATE)));
				subscription.setDeleted(false);

				CarDAO carDAO = new CarDAO();
				List<Car> carsInSubscription = carDAO.findAll(subscription);
				subscription.setCars(carsInSubscription);

				listOfSubscription.add(subscription);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return listOfSubscription;
	}

	/**
	 * Calculate Count(*) for subscription table
	 * 
	 * @param siteEnum
	 * 
	 * @return number of records in subscription talbe
	 */
	public int findTotalNumOfSubscriptions(SiteEnum siteEnum)
	{
		int count = 0;

		String query = SQLQueries.SELECT_ALL + SQLQueries.FROM + " " + TableNames.SUBSCRIPTION + " " + SQLQueries.WHERE + SubscribtionsTable.SITE_NAME + "=" + "\"" + siteEnum.toString() + "\"";

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while (set.next())
			{
				count++;
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return count;
	}

	// ****************************************************************************************************************************
	// IGenericDAO methods
	// ****************************************************************************************************************************
	@Override
	public Integer findMaxID()
	{
		Integer subscriptionID = null;
		String query = String.format(SQLQueries.SELECT_MAX__VALUE__AS__NAME__FROM__TABLE_NAME__, SubscribtionsTable.ID, SQLQueries.MAX_ID, TableNames.SUBSCRIPTION);

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if (set.next())
			{
				subscriptionID = set.getInt("maxID");
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		if (subscriptionID == null)
		{
			subscriptionID = 0;
		}

		return ++subscriptionID;
	}

	@Override
	public Subscription create()
	{
		return new Subscription();
	}

	/**
	 * Not implemented
	 */
	@Override
	public Subscription findByEntity(Customer entity)
	{
		return null;
	}

	@Override
	public Subscription findByID(int ID)
	{
		return this.findByID(String.valueOf(ID));
	}

	@Override
	public Subscription findByID(String ID)
	{
		String query = String.format(SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_, TableNames.SUBSCRIPTION, SubscribtionsTable.ID + "=" + ID);

		Subscription subscription = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if (set.next())
			{
				subscription = this.create();

				subscription.setSubscribtionID(set.getInt(SubscribtionsTable.ID));
				subscription.setCustomerID(set.getInt(SubscribtionsTable.CUSTOMER_ID));
				subscription.setSite(SiteEnum.convert(set.getString(SubscribtionsTable.SITE_NAME)));
				subscription.setSubscriptionType(SubscriptionType.convert(set.getString(SubscribtionsTable.TYPE)));
				subscription.setStartDate(new DateTime(set.getTimestamp(SubscribtionsTable.START_DATE)));
				subscription.setEndDate(new DateTime(set.getTimestamp(SubscribtionsTable.END_DATE)));
				subscription.setEstimatedLeaveDate(new DateTime(set.getTimestamp(SubscribtionsTable.ESTIMATED_LEAVE_DATE)));
				subscription.setArrivalDate(new DateTime(set.getTimestamp(SubscribtionsTable.ARRIVAL_DATE)));
				subscription.setDeleted(false);

				CarDAO carDAO = new CarDAO();
				List<Car> carsInSubscription = carDAO.findAll(subscription);
				subscription.setCars(carsInSubscription);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return subscription;
	}

	@Override
	public List<Subscription> findAll(Customer info)
	{
		String query = String.format(SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_, TableNames.SUBSCRIPTION, SubscribtionsTable.CUSTOMER_ID + "=" + String.valueOf(info.getCustomerID()));

		List<Subscription> listOfSubscription = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfSubscription = new ArrayList<>();
			while (set.next())
			{
				Subscription subscription = this.create();

				subscription.setSubscribtionID(set.getInt(SubscribtionsTable.ID));
				subscription.setCustomerID(info.getCustomerID());
				subscription.setSite(SiteEnum.convert(set.getString(SubscribtionsTable.SITE_NAME)));
				subscription.setSubscriptionType(SubscriptionType.convert(set.getString(SubscribtionsTable.TYPE)));
				subscription.setStartDate(new DateTime(set.getTimestamp(SubscribtionsTable.START_DATE)));
				subscription.setEndDate(new DateTime(set.getTimestamp(SubscribtionsTable.END_DATE)));
				subscription.setEstimatedLeaveDate(new DateTime(set.getTimestamp(SubscribtionsTable.ESTIMATED_LEAVE_DATE)));
				subscription.setArrivalDate(new DateTime(set.getTimestamp(SubscribtionsTable.ARRIVAL_DATE)));
				subscription.setDeleted(false);

				CarDAO carDAO = new CarDAO();
				List<Car> carsInSubscription = carDAO.findAll(subscription);
				subscription.setCars(carsInSubscription);

				listOfSubscription.add(subscription);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return listOfSubscription;
	}

	@Override
	public boolean save(Subscription entity)
	{
		boolean result = false;

		String valuesSet = String.format("%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"", entity.getSubscribtionID(), entity.getCustomerID(), entity.getSite().toString(), entity
				.getSubscriptionType().toString(), entity.getStartDate() != null ? new Timestamp(entity.getStartDate().getMillis()) : new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis()),
				entity.getEndDate() != null ? new Timestamp(entity.getEndDate().getMillis()) : new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis()), entity.getArrivalDate() != null
						? new Timestamp(entity.getArrivalDate().getMillis())
						: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis()), entity.getEstimatedLeaveDate() != null
						? new Timestamp(entity.getEstimatedLeaveDate().getMillis())
						: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis()));

		String sqlQuery = String.format(SQLQueries.INSERT_INTO__TABLENAME__VALUES__, TableNames.SUBSCRIPTION, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return result;
	}

	@Override
	public boolean refresh(String sqlQuery)
	{
		boolean success = false;

		try
		{
			success = DBAdapter.getAdapter().refresh(sqlQuery);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return success;
	}

	@Override
	public boolean deleteByID(int ID)
	{
		return this.deleteByID(String.valueOf(ID));
	}

	@Override
	public boolean deleteByID(String ID)
	{
		String sqlQuery = String.format(SQLQueries.DELETE_FROM__TABLENAME__WHERE__COMPARE_, TableNames.SUBSCRIPTION, SubscribtionsTable.ID + "=" + ID);

		boolean success = false;
		try
		{
			success = DBAdapter.getAdapter().delete(sqlQuery);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return success;
	}
}
